﻿using System;
using System.Data;
using BEGDC.Utility.Castle;
using BEGDC.Utility.NHibernate;
using Castle.Services.Transaction;
using NHibernate;
using NHibernate.Expression;
using Nullables;

namespace BEGDC.PreContract.BLL
{
    public partial class PreContractInfo : IPreContractInfo
    {
        public const string SQL_SELECT_CONTRACTTRANSFER = @"SELECT PreContractInfo.PreContractID, PreContractInfo.PreContractNo, ContractTransfer.ContractNo, ContractTransfer.TransferDate, 
                      ContractTransfer.TransferMoney, ContractTransfer.TransferType, ContractTransfer.RemainMoney,PreContractInfo.BDID,'' as BDName
                      FROM  ContractTransfer
                      INNER JOIN PreContractInfo ON ContractTransfer.PreContractID = PreContractInfo.PreContractID ";

        public const string SQL_SELECT_CONTRACTDELIVERY = @"SELECT     PreContractInfo.PreContractID, PreContractInfo.PreContractNo, ContractDelivery.Percentage
                            FROM         PreContractInfo INNER JOIN
                      ContractDelivery ON PreContractInfo.PreContractID = ContractDelivery.PreContractID ";

        public const string SQl_SELECT_ALLPRECONTRACT = @" SELECT * FROM vw_PreContractInfo ";

        public const string SQL_SELECT_PRECONTRACTDELIVERY = @" SELECT PreContractNo, DeliveryPercent,DeliveryDate,DeliveryPlan ,AOGDate
                                                                FROM PreContractInfo ";

        public const string SQL_SELECT_ORIONINFO = @" SELECT PreContractNo, PreContractType, CATCode, BDCode, OrionNo, SignDue, CEWPromised, OrionPrice
                                                      FROM vw_PreContractInfo";

        public const string SQL_SELECT_COMMENTS = @" SELECT * FROM vw_PreContractInfoComment";

        public const string SQL_SELECT_TRANSFERREDCONTRACT = "select * from vw_TransferredPreContract";



        public DataTable GetContractTransferInfo(string filter)
        {
            using (ISession session = factory.OpenSession())
            {
                DataSet ds = new DataSet();

                //Create a parameter
                string strSql = SQL_SELECT_CONTRACTTRANSFER;
                if (filter != string.Empty)
                    strSql += " WHERE " + filter;


                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch;
            }
        }

        public DataTable GetContractDelivery(string filter)
        {
            using (ISession session = factory.OpenSession())
            {
                DataSet ds = new DataSet();

                //Create a parameter
                string strSql = SQL_SELECT_CONTRACTDELIVERY;
                if (filter != string.Empty)
                    strSql += " WHERE " + filter;


                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch;
            }
        }

        [Transaction(TransactionMode.Requires)]
        public DAL.PreContractInfo[] GetPreContractByNo(string preContractNo)
        {
            DAL.PreContractInfo[] precontract = DAL.PreContractInfo.FindAll(new EqExpression(DAL.PreContractInfo.CONST_PreContractNo, preContractNo));

            return precontract;
        }


        ////Get by id
        //[Transaction(TransactionMode.Requires)]
        //public BEGDC.PreContract.DAL.PreContractInfo GetPreContractByNo(string preContractNo)
        //{
        //    BEGDC.PreContract.DAL.PreContractInfo precontract = BEGDC.PreContract.DAL.PreContractInfo.FindFirst(new NHibernate.Expression.EqExpression(BEGDC.PreContract.DAL.PreContractInfo.CONST_PreContractNo, preContractNo));

        //    return precontract;
        //}


        [Transaction(TransactionMode.Requires)]
        public DAL.PreContractInfo GetPreContractByNo(string preContractNo, string bdcode)
        {
            IBD bdservice = (IBD)AppContainer.Instance[Services.CONST_BD_Service];
            DAL.BD bd = bdservice.GetBDByName(bdcode);

            if (bd == null)
                return null;

            DAL.PreContractInfo precontract = DAL.PreContractInfo.FindFirst(new EqExpression(DAL.PreContractInfo.CONST_PreContractNo, preContractNo), new EqExpression(DAL.PreContractInfo.CONST_BDID, bd));

            return precontract;
        }

        [Transaction(TransactionMode.Requires)]
        public DAL.PreContractInfo GetPreContractByNo(string preContractNo, DAL.BD bd)
        {
            if (bd == null)
                return null;

            DAL.PreContractInfo precontract = DAL.PreContractInfo.FindFirst(new EqExpression(DAL.PreContractInfo.CONST_PreContractNo, preContractNo), new EqExpression(DAL.PreContractInfo.CONST_BDID, bd));

            return precontract;
        }

        [Transaction(TransactionMode.Requires)]
        public DataTable GetPreContractInfo(string filter)
        {
            using (ISession session = factory.OpenSession())
            {
                DataSet ds = new DataSet();

                //Create a parameter
                //Modified by Joan 2009-09-30, for transformed contract, StatusID != 12 is not correct
                string strSql = SQl_SELECT_ALLPRECONTRACT;
                if (filter != string.Empty)
                {
                    //if (filter.IndexOf("StatusID") < 0)
                    //{
                    //    strSql += "Where StatusID != 12 ";
                    //    strSql += " AND " + filter;
                    //}
                    //else
                    //{
                    strSql += "Where ";
                    strSql += filter;
                    //}
                }

                strSql += " ORDER BY StatusID, PreContractNo ";


                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch;
            }
        }

        [Transaction(TransactionMode.Requires)]
        public void UploadPreContractDelivery(DataTable dtUpload, out DataTable dtMissing, out DataTable dtUpdated, out string strMessage)
        {
            strMessage = string.Empty;

            using (ISession session = factory.OpenSession())
            {
                DataSet ds = new DataSet();

                //Create a parameter
                string strSql = SQL_SELECT_PRECONTRACTDELIVERY;

                strSql += " WHERE " + "1=2";

                dtMissing = DAO.ExecuteDataTable(session, strSql);

                dtUpdated = dtMissing.Clone();

                if (dtUpload.Rows.Count == 0) return;

                foreach (DataRow drUpload in dtUpload.Rows)
                {
                    string precontractno = drUpload[0].ToString().Trim();
                    if (precontractno == string.Empty)
                        continue;

                    decimal rate = Math.Round(Convert.ToDecimal(drUpload[1].ToString()) * 100, 2);

                    NullableDateTime dtDeliveryDate = new NullableDateTime();
                    NullableDateTime dtAOGDate = new NullableDateTime();
                    if (drUpload[2].ToString() != string.Empty)
                    {
                        dtDeliveryDate = Convert.ToDateTime(drUpload[2].ToString());
                        //到货时间是发货时间加7天
                        dtAOGDate = Convert.ToDateTime(drUpload[2].ToString()).AddDays(7);
                    }
                    else
                    {
                        dtDeliveryDate = null;
                        dtAOGDate = null;
                    }

                    DAL.PreContractInfo[] precontracts = DAL.PreContractInfo.FindAll(new EqExpression(DAL.PreContractInfo.CONST_PreContractNo, precontractno));

                    //判断预合同是否存在.
                    if (precontracts.Length != 0)
                    {
                        //3. 在dtUpdate中加入一条记录
                        DataRow drUpdated = dtUpdated.NewRow();
                        drUpdated[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                        drUpdated[DAL.PreContractInfo.CONST_DeliveryPercent] = rate;
                        if (dtDeliveryDate == null)
                        {
                            drUpdated[DAL.PreContractInfo.CONST_DeliveryDate] = DBNull.Value;
                            drUpdated[DAL.PreContractInfo.CONST_AOGDate] = DBNull.Value;
                        }
                        else
                        {
                            drUpdated[DAL.PreContractInfo.CONST_DeliveryDate] = Convert.ToDateTime(dtDeliveryDate.ToString());
                            drUpdated[DAL.PreContractInfo.CONST_AOGDate] = Convert.ToDateTime(dtAOGDate.ToString());

                        }
                        drUpdated[DAL.PreContractInfo.CONST_DeliveryPlan] = drUpload[3].ToString();
                        dtUpdated.Rows.Add(drUpdated);

                        //if 存在 
                        //if 大于或等于. 忽略数据
                        foreach (DAL.PreContractInfo precontract in precontracts)
                        {
                            //如果已经转正. 跳过
                            if (precontract.StatusID.DicItemID == (int)DicItem.PreContractStatus.Won)
                                continue;

                            //if (precontract.DeliveryPercent == null || precontract.DeliveryPercent.Value < rate)
                            //{
                            //2. 同时在发货率表加入一条新纪录.
                            DAL.ContractDelivery delivery = new DAL.ContractDelivery();
                            delivery.PreContractID = precontract;
                            delivery.Percentage = rate;
                            delivery.UpdateDate = DateTime.Now;
                            delivery.Create();

                            //precontract.TypeID.DicItemID == 8 借用合同
                            //precontract.TypeID.DicItemID == 9 试用合同
                            if ((precontract.DeliveryPercent == 100 || rate == 100) && (precontract.TypeID.DicItemID == 8 || precontract.TypeID.DicItemID == 9))
                            {
                                precontract.IsDelay = true;
                            }
                            //else 1. 更新预合同的发货率,  
                            //precontract.DeliveryPercent = Math.Round(rate * 100, 2);
                            if (!precontract.DeliveryDate.HasValue)
                            {
                                if (rate >= 0 && rate < 20)
                                {
                                    precontract.Flag = null;
                                }
                                else if (rate >= 20 && rate < 100)
                                {
                                    precontract.Flag = 1;
                                }
                                else if (rate == 100)
                                {
                                    precontract.Flag = 2;
                                }
                                precontract.DeliveryPercent = rate;
                                precontract.DeliveryDate = dtDeliveryDate;
                                precontract.AOGDate = dtAOGDate;

                            }
                            else
                            {
                                if (!precontract.Flag.HasValue)
                                {
                                    precontract.DeliveryPercent = rate;
                                    precontract.DeliveryDate = dtDeliveryDate;
                                    precontract.AOGDate = dtAOGDate;
                                    if (rate >= 20 && rate < 100)
                                    {
                                        precontract.Flag = 1;
                                    }
                                    else if (rate == 100)
                                    {
                                        precontract.Flag = 2;
                                    }
                                }
                                else if (precontract.Flag == 1)
                                {
                                    if (rate >= 0 && rate < 20)
                                    {
                                        precontract.DeliveryPercent = rate;
                                        precontract.DeliveryDate = dtDeliveryDate;
                                        precontract.AOGDate = dtAOGDate;
                                        precontract.Flag = null;
                                    }
                                    else if (rate >= 20 && rate < 100)
                                    {
                                        precontract.DeliveryPercent = rate;
                                    }
                                    else if (rate == 100)
                                    {
                                        precontract.DeliveryPercent = rate;
                                        precontract.DeliveryDate = dtDeliveryDate;
                                        precontract.AOGDate = dtAOGDate;
                                        precontract.Flag = 2;
                                    }
                                }
                                else if (precontract.Flag == 2) //added by yuliang 2010-5-17 当deleveryRate == 100%后人仍可以更改原数据
                                {
                                    if (rate >= 0 && rate < 20)
                                        precontract.Flag = null;
                                    else if (rate >= 20 && rate < 100)
                                        precontract.Flag = 1;

                                    precontract.DeliveryPercent = rate;
                                    precontract.DeliveryDate = dtDeliveryDate;
                                    precontract.AOGDate = dtAOGDate;
                                }
                            }
                            if (rate == 100 || rate == 100.00M)
                                precontract.Delivered = true;
                            else
                                precontract.Delivered = false; //added by yuliang 2010-5-17

                            precontract.DeliveryPlan = drUpload[3].ToString();
                            precontract.ModifiedDate = DateTime.Now;
                            precontract.Update();

                            //}
                        }

                    }
                    else
                    {
                        //else 不存在 1. 在dtMissing里加入一条记录
                        DataRow drMissing = dtMissing.NewRow();
                        drMissing[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                        drMissing[DAL.PreContractInfo.CONST_DeliveryPercent] = rate;
                        if (dtDeliveryDate == null)
                        {
                            drMissing[DAL.PreContractInfo.CONST_DeliveryDate] = DBNull.Value;
                            drMissing[DAL.PreContractInfo.CONST_AOGDate] = DBNull.Value;
                        }
                        else
                        {
                            drMissing[DAL.PreContractInfo.CONST_DeliveryDate] = Convert.ToDateTime(dtDeliveryDate.ToString());
                            drMissing[DAL.PreContractInfo.CONST_AOGDate] = Convert.ToDateTime(dtDeliveryDate.ToString()).AddDays(7);
                        }
                        drMissing[DAL.PreContractInfo.CONST_DeliveryPlan] = drUpload[3].ToString();
                        dtMissing.Rows.Add(drMissing);
                    }
                }

            }
        }

        [Transaction(TransactionMode.Requires)]
        public void UploadOrionInfo(DataTable dtUpload, out DataTable dtMissing, out DataTable dtUpdated, out DataTable dtNotExisted, out string strMessage)
        {
            strMessage = string.Empty;

            using (ISession session = factory.OpenSession())
            {
                DataSet ds = new DataSet();

                //Create a parameter
                string strSql = SQL_SELECT_ORIONINFO;

                strSql += " WHERE " + "1=2";

                dtMissing = DAO.ExecuteDataTable(session, strSql);

                dtUpdated = dtMissing.Clone();

                strSql = SQL_SELECT_ORIONINFO + " WHERE StatusID = 10 Or StatusID = 11";

                dtNotExisted = DAO.ExecuteDataTable(session, strSql);

                if (dtUpdated == null) return;

                DataTable dtUploadClone = dtUpload.Copy();

                ICloseDate closedateService = (ICloseDate)AppContainer.Instance[Services.CONST_CloseDate_Service];


                //循环上传的数据.
                foreach (DataRow drUpload in dtUpload.Rows)
                {
                    string precontractno = drUpload[0].ToString().Trim();
                    if (precontractno == string.Empty)
                        continue;
                    string bd = drUpload[1].ToString();
                    DateTime signdue = Convert.ToDateTime(drUpload[2].ToString());
                    decimal orionorice = Convert.ToDecimal(drUpload[3].ToString());

                    DAL.BD bdentity = DAL.BD.FindFirst(new EqExpression(DAL.BD.CONST_BDCode, bd));

                    if (bdentity == null) //bd不存在. 
                    {
                        //else 不存在 1. 在dtMissing里加入一条记录
                        DataRow drMissing = dtMissing.NewRow();
                        drMissing[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                        drMissing[DAL.BD.CONST_BDCode] = bd;
                        drMissing[DAL.PreContractInfo.CONST_SignDue] = signdue;
                        drMissing[DAL.PreContractInfo.CONST_OrionPrice] = orionorice;
                        dtMissing.Rows.Add(drMissing);

                        continue;
                    }

                    DAL.PreContractInfo precontract = DAL.PreContractInfo.FindFirst(new EqExpression(DAL.PreContractInfo.CONST_PreContractNo, precontractno), new EqExpression(DAL.PreContractInfo.CONST_BDID, bdentity));

                    //判断预合同是否存在.
                    if (precontract != null)
                    {

                        //if 存在 
                        //判断上传的数据中PreContractNo和BD是否有2条以上的数据, 如果有Orion金额必须加在一起后, 再更新数据
                        dtUploadClone.DefaultView.RowFilter = string.Format(" PreContractNo = '{0}' And BD = '{1}'", precontract.PreContractNo, precontract.BDID.BDCode);
                        if (dtUploadClone.DefaultView.Count > 1)
                        {
                            orionorice = 0;
                            foreach (DataRowView drClone in dtUploadClone.DefaultView)
                            {
                                orionorice += Convert.ToDecimal(drClone.Row[3].ToString());
                            }
                        }

                        //如果已经转正. 跳过
                        if (precontract.StatusID.DicItemID == (int)DicItem.PreContractStatus.Won)
                        {
                            continue;
                        }
                        else
                        {
                            //else 1. 更新预合同的Orion金额,  
                            precontract.OrionPrice = Math.Round(orionorice * Convert.ToDecimal(1.17), 2);
                            precontract.SignDue = signdue;
                            precontract.TradeMonth = closedateService.GetTradeMonth_Date(signdue);

                            precontract.ModifiedDate = DateTime.Now;
                            precontract.Update();

                        }


                        //3. 在dtUpdate中加入一条记录
                        DataRow drUpdated = dtUpdated.NewRow();
                        drUpdated[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                        drUpdated[DAL.BD.CONST_BDCode] = bd;
                        drUpdated[DAL.PreContractInfo.CONST_SignDue] = signdue;
                        drUpdated[DAL.PreContractInfo.CONST_OrionPrice] = orionorice;
                        dtUpdated.Rows.Add(drUpdated);

                        dtNotExisted.DefaultView.RowFilter = string.Format(" PreContractNo = '{0}' And BDCode = '{1}'", precontract.PreContractNo, precontract.BDID.BDCode);

                        if (dtNotExisted.DefaultView.Count > 0)
                        {
                            dtNotExisted.Rows.Remove(dtNotExisted.DefaultView[0].Row);

                        }
                    }
                    else
                    {
                        //else 不存在 1. 在dtMissing里加入一条记录
                        DataRow drMissing = dtMissing.NewRow();
                        drMissing[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                        drMissing[DAL.BD.CONST_BDCode] = bd;
                        drMissing[DAL.PreContractInfo.CONST_SignDue] = signdue;
                        drMissing[DAL.PreContractInfo.CONST_OrionPrice] = orionorice;
                        dtMissing.Rows.Add(drMissing);
                    }
                }

                dtNotExisted.DefaultView.RowFilter = string.Empty;

            }
        }

        public void ImportPreContract(DataTable dtUpload, out DataTable dtNew, out DataTable dtDiff, out DataTable dtUpdated, out string strMessage)
        {
            strMessage = string.Empty;

            dtNew = new DataTable();
            dtDiff = new DataTable();
            dtUpdated = new DataTable();

            try
            {

                string strIds = string.Empty; //最后查询需要用的ID.
                string strDifferentIds = string.Empty;

                IPreContractInfo precontractServuce = (IPreContractInfo)AppContainer.Instance[Services.CONST_PreContractInfo_Service];
                IDicItem dicitemService = (IDicItem)AppContainer.Instance[Services.CONST_DicItem_Service];

                dtNew = precontractServuce.GetPreContractInfo(" 1 = 2 ");
                dtDiff = dtNew.Clone();
                dtUpdated = dtNew.Clone();

                if (dtUpload != null)
                {
                    foreach (DataRow dr in dtUpload.Rows)
                    {
                        string strContractNumber = dr["ContractNumber"].ToString();

                        if (strContractNumber == string.Empty)
                            continue;

                        string strContractType = dr["ContractType"].ToString();
                        string strBD = dr["BD"].ToString();
                        string strProvince = dr["Province"].ToString();
                        string strContractName = dr["ContractName"].ToString();
                        string strProjectName = dr["ProjectName"].ToString();
                        string strEstimatedDate = dr["EstimatedDate"].ToString();
                        string strPrice = dr["Price"].ToString();
                        string strProductName = dr["ProductName"].ToString();
                        string strAcceptTime = dr["AcceptTime"].ToString();
                        string strOrionNumber = dr["OrionNumber"].ToString();
                        string strEndACR = dr["EndACR"].ToString();
                        string strEndCustomer = dr["EndCustomer"].ToString();
                        string strApplicant = dr["Applicant"].ToString();
                        string strEmail = dr["Email"].ToString();
                        string strPrincipal = dr["Principal"].ToString();
                        string strPrincipalMail = dr["PrincipalMail"].ToString();
                        string strCAT = strContractNumber.Substring(strContractNumber.IndexOf("-") - 1, 1);

                        //added by yuliang 2010-5-24
                        string strUpdateReason = String.Empty;
                        if (dr["UpdateReason"] != DBNull.Value)
                            strUpdateReason = dr["UpdateReason"].ToString();

                        //string strRemainAmount = String.Empty;
                        //if(dr["RemainAmount"] != DBNull.Value)
                        //    strRemainAmount = dr["RemainAmount"].ToString();


                        //在数据库找预合同, 如果没找到直接做Insert, 
                        //如果找到, 做数据比较. 
                        //如果承诺时间, 承诺金额, Orion编号, ACR No, 客户名称一致的话, 直接Update. 否则需求管理员确认.
                        DAL.PreContractInfo precontract = precontractServuce.GetPreContractByNo(strContractNumber, strBD);

                        bool isAddNew = false;
                        bool isUpdate = false;

                        if (precontract == null)    //在数据库中没有找到该预合同号.
                        {
                            isAddNew = true;
                            precontract = new DAL.PreContractInfo();

                            precontract.AdvancedContract = string.Empty;
                            precontract.DeliveryPercent = 0;

                            DAL.DicItem status = dicitemService.GetDicItem((int)DicItem.PreContractStatus.Pending);
                            precontract.StatusID = status;

                            precontract.SalePrice = 0;

                            string asborlucent = strContractNumber.Substring(4, 1);
                            if (asborlucent == "0")
                                precontract.ASBorLucent = false;
                            else
                                precontract.ASBorLucent = true;

                            if (strEstimatedDate != string.Empty)
                            {
                                ICloseDate closedateservice = (ICloseDate)AppContainer.Instance[Services.CONST_CloseDate_Service];
                                precontract.TradeMonth = closedateservice.GetTradeMonth_Date(Convert.ToDateTime(strEstimatedDate));
                            }

                            if (strPrice == string.Empty)
                                precontract.CEWPromised = 0;
                            else
                                precontract.CEWPromised = new NullableDecimal(Convert.ToDecimal(strPrice));

                            precontract.OrionPrice = precontract.CEWPromised;
                            precontract.SparePrice = precontract.CEWPromised;//added by yuliang 2010-5-24

                            //added by yuliang 2010-8-26
                            //if (!String.IsNullOrEmpty(strRemainAmount))
                            //    precontract.SparePrice = decimal.Parse(strRemainAmount);

                            if (strEstimatedDate != string.Empty)
                            {
                                precontract.SignDue = new NullableDateTime(Convert.ToDateTime(strEstimatedDate));
                                precontract.PromisedDate = Convert.ToDateTime(strEstimatedDate);
                            }
                            else
                                precontract.SignDue = null;

                            precontract.ACRNo = strEndACR;

                            precontract.CustomerName = strEndCustomer;

                            precontract.OrionNo = strOrionNumber;

                        }
                        else
                        {
                            isUpdate = true;
                            //if (precontract.PromisedDate == Convert.ToDateTime(strEstimatedDate) &&
                            //    precontract.CEWPromised == Convert.ToDecimal(strPrice) &&
                            //    precontract.OrionNo == strOrionNumber &&
                            //    precontract.ACRNo == strEndACR &&
                            //    precontract.CustomerName == strEndCustomer)
                            //{
                            //    isUpdate = true;
                            //}
                            //else
                            //{
                            //strDifferentIds += precontract.PreContractID.ToString() + ",";
                            //continue;
                            //}
                        }


                        //BD
                        IBD bdservice = (IBD)AppContainer.Instance[Services.CONST_BD_Service];
                        DAL.BD bd = bdservice.GetBDByName(strBD);
                        if (bd == null)
                        {
                            strMessage = "<script>alert('" + string.Format("BD: {0} 在数据库中没有找到, 请先在系统中维护BD数据.\\n\\nBD: {0} not found, Please maintenance BD data at system management.", strBD) + "')</script>";
                            return;
                        }
                        precontract.BDID = bd;
                        dr["BD"] = bd.BDCode;

                        //Province
                        IProvince provinceservice = (IProvince)AppContainer.Instance[Services.CONST_Province_Service];
                        DAL.Province province = null;
                        if (strContractNumber.ToUpper().StartsWith("BJ"))
                        {
                            if (strCAT.ToUpper() == "M")
                                province = provinceservice.GetProvinceByName("BJCM");
                            else if (strCAT.ToUpper() == "T")
                                province = provinceservice.GetProvinceByName("BJCT");
                            else if (strCAT.ToUpper() == "U")
                                province = provinceservice.GetProvinceByName("BJCU");
                            else if (strCAT.ToUpper() == "R")
                                province = provinceservice.GetProvinceByName("BJCM");
                            else if (strCAT.ToUpper() == "N")
                                province = provinceservice.GetProvinceByName("BJCU");
                        }
                        else if (strContractNumber.ToUpper().StartsWith("CM") || strContractNumber.ToUpper().StartsWith("CR"))
                        {
                            province = provinceservice.GetProvinceByName("CMHQ");
                        }
                        else if (strContractNumber.ToUpper().StartsWith("CT"))
                        {
                            province = provinceservice.GetProvinceByName("CTHQ");
                        }
                        else if (strContractNumber.ToUpper().StartsWith("CU"))
                        {
                            province = provinceservice.GetProvinceByName("CUHQ");
                        }
                        else if (strContractNumber.ToUpper().StartsWith("CV"))
                        {
                            province = provinceservice.GetProvinceByName("CVM");
                        }
                        else
                        {
                            province = provinceservice.GetProvinceByName(strProvince);
                        }
                        if (province == null)
                        {
                            strMessage = "<script>alert('" + string.Format("省: {0} 在数据库中没有找到, 请先在系统中维护省数据.\\n\\nProvince: {0} not found, Please maintenance Province data at system management", strProvince) + "')</script>";
                            return;
                        }
                        precontract.ProvinceID = province;

                        //CAT
                        ICAT catservice = (ICAT)AppContainer.Instance[Services.CONST_CAT_Service];
                        DAL.CAT cat = catservice.GetCATByShorteningCode(strCAT);
                        if (cat == null)
                        {
                            strMessage = "<script>alert('" + string.Format("CAT: {0} 在数据库中没有找到, 请先在系统中维护CAT数据.\\n\\nCAT: {0} not found, Please maintenance CAT data at system management.", strCAT) + "')</script>";
                            return;
                        }
                        precontract.CATID = cat;

                        //Product
                        IProduct productservice = (IProduct)AppContainer.Instance[Services.CONST_Product_Service];
                        DAL.Product product = productservice.GetProductByName(strProductName, true);
                        if (product == null)
                        {
                            strMessage = "<script>alert('" + string.Format("产品: {0} 在数据库中没有找到, 请先在系统中维护产品数据.\\n\\nProduct: {0} not found, Please maintenance Product data at system management.", strCAT) + "')</script>";
                            return;
                        }
                        precontract.ProductID = product;

                        precontract.PreContractNo = strContractNumber;

                        DAL.DicItem type = new DAL.DicItem();
                        DAL.DicItem adjust = new DAL.DicItem();//added by yuliang 2010-5-24
                        if (strContractType == "1")
                        {
                            type = dicitemService.GetDicItem(16);//changed by yuliang //type = dicitemService.GetDicItem(7);
                            adjust = dicitemService.GetDicItem(21);//added by yuliang 2010-5-24
                        }
                        else if (strContractType == "2")
                        {
                            type = dicitemService.GetDicItem(8);
                            adjust = dicitemService.GetDicItem(20);//added by yuliang 2010-5-24
                        }
                        else if (strContractType == "3")
                        {
                            type = dicitemService.GetDicItem(9);
                            adjust = dicitemService.GetDicItem(19);//added by yuliang 2010-5-24
                        }
                        else if (strContractType == "4")
                        {
                            type = dicitemService.GetDicItem(14);
                            adjust = dicitemService.GetDicItem(18);//added by yuliang 2010-5-24
                        }
                        //added by yuliang 2010-5-24
                        else if (strContractType == "11")
                        {
                            type = dicitemService.GetDicItem(15);
                            adjust = dicitemService.GetDicItem(21);
                        }
                        else if (strContractType == "12")
                        {
                            type = dicitemService.GetDicItem(16);
                            adjust = dicitemService.GetDicItem(21);
                        }
                        else if (strContractType == "13")
                        {
                            type = dicitemService.GetDicItem(17);
                            adjust = dicitemService.GetDicItem(21);
                        }
                        precontract.TypeID = type;
                        precontract.AdjustID = adjust;//added by yuliang 2010-5-24

                        precontract.ContractName = strContractName;

                        precontract.Applicant = strApplicant;
                        precontract.ApplicantEmail = strEmail;
                        precontract.Principal = strPrincipal;
                        precontract.PrincipalEmail = strPrincipalMail;
                        precontract.ModifiedDate = DateTime.Now;
                        precontract.ERPReceived = Convert.ToDateTime(strAcceptTime);

                        if (!String.IsNullOrEmpty(strUpdateReason))
                            precontract.UpdateReason = precontract.UpdateReason + ";" + strUpdateReason + "(" + strAcceptTime + ")";//added by yuliang 2010-5-24

                        DataRow drNew = dtNew.NewRow();

                        drNew[DAL.PreContractInfo.CONST_PreContractID] = precontract.PreContractID;
                        drNew[DAL.PreContractInfo.CONST_PreContractNo] = precontract.PreContractNo;
                        drNew["PreContractStatus"] = precontract.StatusID.DicItemValue;
                        drNew["PreContractType"] = precontract.TypeID.DicItemValue;
                        drNew["PreContractAdjust"] = precontract.AdjustID.DicItemValue;//added by yuliang 2010-5-24
                        drNew[DAL.Product.CONST_ProductName] = precontract.ProductID.ProductName;
                        if (precontract.PromisedDate.HasValue)
                            drNew[DAL.PreContractInfo.CONST_PromisedDate] = precontract.PromisedDate.Value.ToString("yyyy-MM-dd");

                        if (precontract.CEWPromised.HasValue)
                            drNew[DAL.PreContractInfo.CONST_CEWPromised] = precontract.CEWPromised.Value;
                        if (precontract.OrionPrice.HasValue)
                            drNew[DAL.PreContractInfo.CONST_OrionPrice] = precontract.OrionPrice.Value;

                        if (precontract.SalePrice.HasValue)
                            drNew[DAL.PreContractInfo.CONST_SalePrice] = precontract.SalePrice.Value;
                        else
                            drNew[DAL.PreContractInfo.CONST_SalePrice] = 0;

                        if (precontract.SignDue.HasValue)
                            drNew[DAL.PreContractInfo.CONST_SignDue] = precontract.SignDue.Value.ToString("yyyy-MM-dd");
                        if (precontract.SignActual.HasValue)
                            drNew[DAL.PreContractInfo.CONST_SignActual] = precontract.SignActual.Value.ToString("yyyy-MM-dd");

                        drNew[DAL.PreContractInfo.CONST_CustomerName] = precontract.CustomerName;
                        drNew[DAL.PreContractInfo.CONST_ContractName] = precontract.ContractName;
                        if (precontract.ERPReceived.HasValue)
                            drNew[DAL.PreContractInfo.CONST_ERPReceived] = precontract.ERPReceived.Value.ToString("yyyy-MM-dd");
                        drNew[DAL.PreContractInfo.CONST_OrionNo] = precontract.OrionNo;
                        drNew[DAL.PreContractInfo.CONST_Delivered] = precontract.Delivered;
                        drNew[DAL.PreContractInfo.CONST_CurrentSituation] = precontract.CurrentSituation;
                        if (precontract.BlackDate.HasValue)
                            drNew[DAL.PreContractInfo.CONST_BlackDate] = precontract.BlackDate.Value.ToString("yyyy-MM-dd");

                        if (precontract.ModifiedDate.HasValue)
                            drNew[DAL.PreContractInfo.CONST_ModifiedDate] = precontract.ModifiedDate.Value.ToString();
                        drNew[DAL.PreContractInfo.CONST_ACRNo] = precontract.ACRNo;
                        drNew[DAL.PreContractInfo.CONST_ASBorLucent] = precontract.ASBorLucent;
                        if (!precontract.ASBorLucent)
                            drNew["ASBorLucentName"] = "ASB";
                        else
                            drNew["ASBorLucentName"] = "Lucent";

                        drNew[DAL.PreContractInfo.CONST_Applicant] = precontract.Applicant;
                        drNew[DAL.PreContractInfo.CONST_ApplicantEmail] = precontract.ApplicantEmail;

                        drNew[DAL.PreContractInfo.CONST_Principal] = precontract.Principal;
                        drNew[DAL.PreContractInfo.CONST_PrincipalEmail] = precontract.PrincipalEmail;
                        drNew[DAL.PreContractInfo.CONST_ProvinceID] = precontract.ProvinceID.ProvinceID;
                        drNew[DAL.PreContractInfo.CONST_CATID] = precontract.CATID.CATID;
                        drNew[DAL.PreContractInfo.CONST_BDID] = precontract.BDID.BDID;

                        drNew[DAL.Province.CONST_ProvinceCode] = precontract.ProvinceID.ProvinceCode;
                        drNew[DAL.CAT.CONST_CATCode] = precontract.CATID.CATCode;
                        drNew[DAL.BD.CONST_BDCode] = precontract.BDID.BDCode;

                        if (precontract.TradeMonth.HasValue)
                            drNew[DAL.PreContractInfo.CONST_TradeMonth] = precontract.TradeMonth.Value.ToString("MM");

                        //
                        //added by yuliang 2010-5-24
                        if (precontract.SparePrice.HasValue)
                            drNew[DAL.PreContractInfo.CONST_SparePrice] = precontract.SparePrice.Value;

                        drNew[DAL.PreContractInfo.CONST_UpdateReason] = precontract.UpdateReason = precontract.UpdateReason + ";" + strUpdateReason + "(" + strAcceptTime + ")";//added by yuliang 2010-5-24

                        if (isAddNew || isUpdate)
                        {
                            if (isAddNew)
                            {
                                //precontract.CreateAndFlush();
                                dtNew.Rows.Add(drNew.ItemArray);
                            }
                            if (isUpdate)
                            {
                                dtDiff.Rows.Add(drNew.ItemArray);
                                //precontract.Update();
                            }

                            strIds += precontract.PreContractID.ToString() + ",";

                        }
                        else
                        {
                            ////把不同的预合同存入HishTable
                            //BEGDC.PreContract.DAL.PreContractInfo temp = precontract.Clone();
                            //temp.PreContractID = precontract.PreContractID;

                            //arrValidatePreContract.Add(precontract.PreContractID, temp);

                            //strDifferentIds += precontract.PreContractID.ToString() + ",";
                        }

                        precontract = new DAL.PreContractInfo();
                        precontract = null;

                    }
                }

                //得到新增和更新的预合同DataTable
                //if (strIds == string.Empty)
                //    strIds = "0";
                //else
                //    strIds = strIds.Substring(0, strIds.Length - 1);
                //dtUpdated = precontractServuce.GetPreContractInfo(string.Format(" PreContractID in ({0}) ", strIds));

                //得到需要确认的预合同DataTable
                //if (strDifferentIds == string.Empty)
                //    strDifferentIds = "0";
                //else
                //    strDifferentIds = strDifferentIds.Substring(0, strDifferentIds.Length - 1);
                //dtDiff = precontractServuce.GetPreContractInfo(string.Format(" PreContractID in ({0}) ", strDifferentIds));

            }
            catch (Exception ex)
            {

                strMessage = "<script>alert('" + ex.Message.Replace("'", "\\'") + "')</script>";
            }

        }


        [Transaction(TransactionMode.Requires)]
        public void UploadPreContractCFI(DataTable dtUpload, out DataTable dtMissing, out DataTable dtUpdated, out string strMessage)
        {
            strMessage = string.Empty;
            BEGDC.PreContract.BLL.IBD ibd = (BEGDC.PreContract.BLL.IBD)BEGDC.Utility.Castle.AppContainer.Instance[BEGDC.PreContract.BLL.Services.CONST_BD_Service];

            using (ISession session = factory.OpenSession())
            {
                DataSet ds = new DataSet();

                //Create a parameter
                string strSql = @" SELECT PreContractNo,'' as BD,CFIPrice
                                   FROM PreContractInfo ";

                strSql += " WHERE " + "1=2";

                //return empty table
                dtMissing = DAO.ExecuteDataTable(session, strSql);
                dtUpdated = dtMissing.Clone();

                if (dtUpload.Rows.Count == 0)
                    return;

                foreach (DataRow drUpload in dtUpload.Rows)
                {
                    string precontractno = drUpload[0].ToString().Trim();
                    if (precontractno == string.Empty)
                        continue;

                    //decimal rate = Math.Round(Convert.ToDecimal(drUpload[1].ToString()) * 100, 2);
                    //NullableDateTime dtDeliveryDate = new NullableDateTime();
                    //if (drUpload[2].ToString() != string.Empty)
                    //    dtDeliveryDate = Convert.ToDateTime(drUpload[2].ToString());
                    //else
                    //{
                    //    dtDeliveryDate = null;
                    //}

                    //
                    BEGDC.PreContract.DAL.BD bd = ibd.GetBDByName(drUpload[1].ToString().Trim());
                    if (bd == null)
                    {
                        DataRow drMissing = dtMissing.NewRow();
                        drMissing[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                        drMissing["BD"] = bd.BDCode;
                        drMissing[DAL.PreContractInfo.CONST_CFIPrice] = Convert.ToDecimal(drUpload[2].ToString());
                        dtMissing.Rows.Add(drMissing);
                    }

                    DataTable contrTemp = GetByContractNoAndBDID(precontractno, bd.BDID);
                    //DAL.PreContractInfo[] precontracts = DAL.PreContractInfo.FindAll(new NHibernate.Expression.AndExpression(new EqExpression(DAL.PreContractInfo.CONST_PreContractNo, precontractno), new EqExpression(DAL.PreContractInfo.CONST_BDID, bd.BDID)));

                    //判断预合同是否存在.
                    if (contrTemp != null && contrTemp.Rows.Count > 0)//(precontracts.Length != 0)
                    {
                        //3. 在dtUpdate中加入一条记录
                        DataRow drUpdated = dtUpdated.NewRow();
                        drUpdated[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                        drUpdated["BD"] = bd.BDCode;
                        drUpdated[DAL.PreContractInfo.CONST_CFIPrice] = Convert.ToDecimal(drUpload[2].ToString());
                        dtUpdated.Rows.Add(drUpdated);

                        foreach (DataRow dr in contrTemp.Rows)
                        {
                            DAL.PreContractInfo precontract = DAL.PreContractInfo.Find(int.Parse(dr["PreContractID"].ToString()));

                            precontract.CFIPrice = Convert.ToDecimal(drUpload[2].ToString());
                            precontract.ModifiedDate = DateTime.Now;
                            precontract.Update();
                        }
                    }
                    else
                    {
                        //else 不存在 1. 在dtMissing里加入一条记录
                        DataRow drMissing = dtMissing.NewRow();
                        drMissing[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                        drMissing["BD"] = bd.BDCode;
                        drMissing[DAL.PreContractInfo.CONST_CFIPrice] = Convert.ToDecimal(drUpload[2].ToString());
                        dtMissing.Rows.Add(drMissing);
                    }
                }

            }
        }

        public void UpdatePreContract_Import(ref string preContractId, DataRow drUpdate, out string strMessage)
        {
            strMessage = string.Empty;

            DAL.PreContractInfo precontract = null;
            IDicItem dicitemService = (IDicItem)AppContainer.Instance[Services.CONST_DicItem_Service];

            if (preContractId == string.Empty)  //new
            {
                precontract = new DAL.PreContractInfo();
            }
            else
            {
                precontract = DAL.PreContractInfo.FindFirst(new EqExpression(DAL.PreContractInfo.CONST_PreContractID, Convert.ToInt32(preContractId)));
            }
            //判断预合同是否存在.
            if (precontract == null)
            {
                return;
            }
            DataRow dr = drUpdate;

            string strContractNumber = dr["ContractNumber"].ToString().Trim();

            string strContractType = dr["ContractType"].ToString();
            string strBD = dr["BD"].ToString();
            string strProvince = dr["Province"].ToString();
            string strContractName = dr["ContractName"].ToString();
            string strProjectName = dr["ProjectName"].ToString();
            string strEstimatedDate = dr["EstimatedDate"].ToString();
            string strPrice = dr["Price"].ToString();
            string strProductName = dr["ProductName"].ToString();
            string strAcceptTime = dr["AcceptTime"].ToString();
            string strOrionNumber = dr["OrionNumber"].ToString();
            string strEndACR = dr["EndACR"].ToString();
            string strEndCustomer = dr["EndCustomer"].ToString();
            string strApplicant = dr["Applicant"].ToString();
            string strEmail = dr["Email"].ToString();
            string strPrincipal = dr["Principal"].ToString();
            string strPrincipalMail = dr["PrincipalMail"].ToString();
            string strCAT = strContractNumber.Substring(strContractNumber.Length - 6, 1);
            string strSpecialApprove = dr["SpecialApprove"].ToString();

            //added by yuliang 2010-5-24
            string strUpdateReason = String.Empty;
            if (dr["UpdateReason"] != DBNull.Value)
                strUpdateReason = dr["UpdateReason"].ToString();


            //在数据库找预合同, 如果没找到直接做Insert, 
            //如果找到, 做数据比较. 
            //如果承诺时间, 承诺金额, Orion编号, ACR No, 客户名称一致的话, 直接Update. 否则需求管理员确认.

            DAL.DicItem type = new DAL.DicItem();
            DAL.DicItem adjust = new DAL.DicItem();
            if (strContractType == "1")
            {
                type.DicItemID = 16;
                adjust.DicItemID = 21;
            }
            else if (strContractType == "2")
            {
                type.DicItemID = 8;
                adjust.DicItemID = 20;
            }
            else if (strContractType == "3")
            {
                type.DicItemID = 9;
                adjust.DicItemID = 19;
            }
            else if (strContractType == "4")
            {
                type.DicItemID = 14;
                adjust.DicItemID = 18;
            }
            else if (strContractType == "11")
            {
                type.DicItemID = 15;
                adjust.DicItemID = 21;
            }
            else if (strContractType == "12")
            {
                type.DicItemID = 16;
                adjust.DicItemID = 21;
            }
            else if (strContractType == "13")
            {
                type.DicItemID = 17;
                adjust.DicItemID = 21;
            }

            precontract.TypeID = type;

            if (strEstimatedDate.Trim() == string.Empty)
            {
                strMessage = "<script>alert('承诺转正时间不能为空.\\n\\nCygnus Date can not be empty.')</script>";
                return;
            }

            precontract.CEWPromised = new NullableDecimal(Convert.ToDecimal(strPrice));
            precontract.SignDue = new NullableDateTime(Convert.ToDateTime(strEstimatedDate));
            ICloseDate closedateservice = (ICloseDate)AppContainer.Instance[Services.CONST_CloseDate_Service];
            precontract.TradeMonth = closedateservice.GetTradeMonth_Date(precontract.SignDue.Value);

            precontract.PromisedDate = Convert.ToDateTime(strEstimatedDate);
            precontract.SpecialApprove = strSpecialApprove;

            //BD
            IBD bdservice = (IBD)AppContainer.Instance[Services.CONST_BD_Service];
            DAL.BD bd = bdservice.GetBDByName(strBD);
            if (bd == null)
            {
                strMessage = "<script>alert('" + string.Format("BD: {0} 在数据库中没有找到, 请先在系统中维护BD数据.", strBD) + "')</script>";
                return;
            }
            precontract.BDID = bd;

            //Province
            IProvince provinceservice = (IProvince)AppContainer.Instance[Services.CONST_Province_Service];
            DAL.Province province = null;
            if (strContractNumber.ToUpper().StartsWith("BJ"))
            {
                if (strCAT.ToUpper() == "M")
                    province = provinceservice.GetProvinceByName("BJCM");
                else if (strCAT.ToUpper() == "T")
                    province = provinceservice.GetProvinceByName("BJCT");
                else if (strCAT.ToUpper() == "U")
                    province = provinceservice.GetProvinceByName("BJCU");
                else if (strCAT.ToUpper() == "R")
                    province = provinceservice.GetProvinceByName("BJCM");
                else if (strCAT.ToUpper() == "N")
                    province = provinceservice.GetProvinceByName("BJCU");
            }
            else if (strContractNumber.ToUpper().StartsWith("CM") || strContractNumber.ToUpper().StartsWith("CR"))
            {
                province = provinceservice.GetProvinceByName("CMHQ");
            }
            else if (strContractNumber.ToUpper().StartsWith("CT"))
            {
                province = provinceservice.GetProvinceByName("CTHQ");
            }
            else if (strContractNumber.ToUpper().StartsWith("CU"))
            {
                province = provinceservice.GetProvinceByName("CUHQ");
            }
            else if (strContractNumber.ToUpper().StartsWith("CV"))
            {
                province = provinceservice.GetProvinceByName("CVM");
            }
            else
            {
                province = provinceservice.GetProvinceByName(strProvince);
            }
            if (province == null)
            {
                strMessage = "<script>alert('" + string.Format("省: {0} 在数据库中没有找到, 请先在系统中维护省数据.", strProvince) + "')</script>";
                return;
            }
            precontract.ProvinceID = province;

            //CAT
            ICAT catservice = (ICAT)AppContainer.Instance[Services.CONST_CAT_Service];
            DAL.CAT cat = catservice.GetCATByShorteningCode(strCAT);
            if (cat == null)
            {
                strMessage = "<script>alert('" + string.Format("CAT: {0} 在数据库中没有找到, 请先在系统中维护CAT数据.", strCAT) + "')</script>";
                return;
            }
            precontract.CATID = cat;

            //Product
            IProduct productservice = (IProduct)AppContainer.Instance[Services.CONST_Product_Service];
            DAL.Product product = productservice.GetProductByName(strProductName, true);
            if (product == null)
            {
                strMessage = "<script>alert('" + string.Format("产品: {0} 在数据库中没有找到, 请先在系统中维护产品数据.", strCAT) + "')</script>";
                return;
            }
            precontract.ProductID = product;

            precontract.ContractName = strContractName;
            precontract.ACRNo = strEndACR;
            precontract.OrionNo = strOrionNumber;
            precontract.Applicant = strApplicant;
            precontract.ApplicantEmail = strEmail;
            precontract.Principal = strPrincipal;
            precontract.PrincipalEmail = strPrincipalMail;
            precontract.CustomerName = strEndCustomer;
            if (strAcceptTime == string.Empty)
                precontract.ERPReceived = null;
            else
                precontract.ERPReceived = Convert.ToDateTime(strAcceptTime);

            precontract.ModifiedDate = DateTime.Now;

            //added by yuliang 2010-5-24
            precontract.AdjustID = adjust;

            if (!String.IsNullOrEmpty(strUpdateReason))
            {
                precontract.UpdateReason = precontract.UpdateReason + ";" + "(" + Convert.ToDateTime(strAcceptTime).ToString("yyyy-MM-dd") + ")" + strUpdateReason;

                if (precontract.UpdateReason.StartsWith(";"))
                    precontract.UpdateReason = precontract.UpdateReason.TrimStart(';');
            }

            if (preContractId == string.Empty)
            {
                precontract.PreContractNo = strContractNumber;
                precontract.AdvancedContract = string.Empty;
                precontract.DeliveryPercent = 0;
                precontract.OrionPrice = precontract.CEWPromised;

                DAL.DicItem status = dicitemService.GetDicItem((int)DicItem.PreContractStatus.Pending);

                precontract.StatusID = status;

                precontract.SalePrice = 0;

                string asborlucent = strContractNumber.Substring(4, 1);
                if (asborlucent == "0")
                    precontract.ASBorLucent = false;
                else
                    precontract.ASBorLucent = true;

                //added by yuliang 对于新进合同 SparePrice = CEWPromised
                precontract.SparePrice = precontract.CEWPromised;
                precontract.DeliveryPlan = "no plan";

                precontract.CreateAndFlush();
                preContractId = precontract.PreContractID.ToString();

            }
            else
            {
                //added by yuliang 对于新进合同 SparePrice = CEWPromised
                precontract.SparePrice = precontract.CEWPromised;
                //
                precontract.Update();
            }

        }

        public void UploadContractTransfer(DataTable dtUpload, out DataTable dtMissing, out DataTable dtUpdated, out DataTable dtPartlyToFormalMultiBD, out DataTable dtDiffBD, out string strMessage)
        {
            strMessage = string.Empty;

            using (ISession session = factory.OpenSession())
            {
                DataSet ds = new DataSet();

                //Create a parameter
                string strSql = SQL_SELECT_CONTRACTTRANSFER;
                strSql += " WHERE " + "1=2";

                dtMissing = DAO.ExecuteDataTable(session, strSql);
                dtUpdated = dtMissing.Clone();
                dtPartlyToFormalMultiBD = dtMissing.Clone(); // added by yualing 2010-5-25
                dtDiffBD = dtMissing.Clone(); // added by yualing 2010-5-25


                if (dtUpdated == null) return;

                IPreContractInfo precontractservice = (IPreContractInfo)AppContainer.Instance[Services.CONST_PreContractInfo_Service];

                //循环上传的数据.
                foreach (DataRow drUpload in dtUpload.Rows)
                {
                    string contractno = drUpload["FormalContractNo"].ToString().Trim();
                    if (contractno == string.Empty)
                        continue;
                    string precontractno = drUpload["InformalContractNo"].ToString().Trim();
                    string transfertype = drUpload["TransferType"].ToString().Trim();

                    double transferamount = 0;
                    try
                    {
                        transferamount = Convert.ToDouble(drUpload["TransferAmount"].ToString().Trim());
                    }
                    catch { }

                    double remainamount = 0;
                    try
                    {
                        remainamount = Convert.ToDouble(drUpload["RemainAmount"].ToString().Trim());
                    }
                    catch { }

                    DateTime transferdate = Convert.ToDateTime(drUpload["TransferDate"].ToString().Trim());

                    //added by yuliang 2010-08-24
                    int bdID = 0;
                    try
                    {
                        bdID = Convert.ToInt32(drUpload["BDID"].ToString().Trim());
                    }
                    catch { }
                    //string strSpecialApprove = "";
                    //try
                    //{
                    //    strSpecialApprove = Convert.ToString(drUpload["SpecialApprove"]).Trim();
                    //}
                    //catch { }

                    string bdName = String.Empty;
                    bdName = drUpload["BDName"].ToString().Trim();

                    IBD bdservice = (IBD)AppContainer.Instance[Services.CONST_BD_Service];
                    DAL.PreContractInfo[] precontracts = precontractservice.GetPreContractByNo(precontractno);

                    if (precontracts != null && precontracts.Length > 0)
                    {
                        int iCount = precontracts.Length;
                        foreach (DAL.PreContractInfo precontract in precontracts)
                        {
                            //存在
                            //判断Precontract里是否这个转正合同信息了.
                            //if 有. 说明已经转正. 加入更新信息.
                            //else 没有. 更新转正信息. 新增转正信息

                            //在dtUpdated里加入一条记录
                            DataRow drUpdated = dtUpdated.NewRow();
                            drUpdated[DAL.ContractTransfer.CONST_TransferDate] = transferdate;
                            drUpdated[DAL.ContractTransfer.CONST_TransferMoney] = transferamount;
                            drUpdated[DAL.ContractTransfer.CONST_ContractNo] = contractno;
                            drUpdated[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                            drUpdated[DAL.ContractTransfer.CONST_TransferType] = transfertype;
                            drUpdated[DAL.ContractTransfer.CONST_RemainMoney] = remainamount;

                            DAL.BD bd = bdservice.GetBDByName(bdName);
                            if (bd == null && bdName != "---")
                            {
                                strMessage = "<script>alert('" + string.Format("BD: {0} 在数据库中没有找到, 请先在系统中维护BD数据.\\n\\nBD: {0} not found, Please maintenance BD data at system management.", bdName) + "')</script>";
                                return;
                            }
                            if (bdName == "---")
                                drUpdated[DAL.ContractTransfer.CONST_BDID] = 0;
                            else
                                drUpdated[DAL.ContractTransfer.CONST_BDID] = bdID = bd.BDID;
                            drUpdated["BDName"] = bdName;

                            dtUpdated.Rows.Add(drUpdated);

                            //
                            if (transfertype != "全部转入" && precontracts.Length > 1)
                            {
                                //在drPartlyToFormalMultiBD里加入一条记录
                                DataRow drPartlyToFormalMultiBD = dtPartlyToFormalMultiBD.NewRow();
                                drPartlyToFormalMultiBD[DAL.ContractTransfer.CONST_TransferDate] = transferdate;
                                drPartlyToFormalMultiBD[DAL.ContractTransfer.CONST_TransferMoney] = transferamount;
                                drPartlyToFormalMultiBD[DAL.ContractTransfer.CONST_ContractNo] = contractno;
                                drPartlyToFormalMultiBD[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                                drPartlyToFormalMultiBD[DAL.ContractTransfer.CONST_TransferType] = transfertype;
                                drPartlyToFormalMultiBD[DAL.ContractTransfer.CONST_RemainMoney] = remainamount;
                                drPartlyToFormalMultiBD[DAL.ContractTransfer.CONST_BDID] = drUpdated[DAL.ContractTransfer.CONST_BDID];
                                drPartlyToFormalMultiBD["BDName"] = bdName;

                                dtPartlyToFormalMultiBD.Rows.Add(drPartlyToFormalMultiBD);
                            }

                            //如果已经转正. 跳过
                            if (precontract.StatusID.DicItemID == (int)DicItem.PreContractStatus.Won)
                            {
                                iCount--;
                                continue;
                            }

                            if (precontract.AdvancedContract == null)
                                precontract.AdvancedContract = string.Empty;
                            if (precontract.AdvancedContract.IndexOf(contractno) == -1)
                            {
                                if (precontract.AdvancedContract != string.Empty && (!precontract.AdvancedContract.EndsWith(";")))
                                    precontract.AdvancedContract += ";";
                                precontract.AdvancedContract += contractno + ";";

                                DAL.ContractTransfer transfer = new DAL.ContractTransfer();

                                transfer.ContractNo = contractno;
                                transfer.PreContractID = precontract;
                                transfer.RemainMoney = remainamount;
                                transfer.TransferDate = transferdate;
                                transfer.TransferMoney = transferamount;
                                transfer.TransferType = transfertype;
                                transfer.UpdateDate = DateTime.Now;
                                if (bd != null)
                                    transfer.BDID = bd.BDID;
                                else
                                    transfer.BDID = 0;

                                transfer.Create();
                            }

                            if (transfertype == "全部转入") //全部转入用Promisied数据更新SalePrice和OrionPrice
                            {
                                DAL.DicItem status = new DAL.DicItem();
                                status.DicItemID = (int)DicItem.PreContractStatus.Won;
                                precontract.StatusID = status;

                                if (bd != null && bd.BDID == precontract.BDID.BDID)
                                {
                                    //added by yuliang 2010-5-18
                                    precontract.SalePrice = precontract.CEWPromised;
                                    precontract.SparePrice = 0;

                                    //precontract.OrionPrice = precontract.CEWPromised;
                                    //precontract.CEWPromised = 0;
                                    precontract.SignActual = transferdate;
                                    precontract.SignDue = transferdate;

                                    ICloseDate closedateservice = (ICloseDate)AppContainer.Instance[Services.CONST_CloseDate_Service];
                                    precontract.TradeMonth = closedateservice.GetTradeMonth_Date(precontract.SignDue.Value);

                                    //added by yuliang 2010-8-24
                                    precontract.TransferHistoryInfo += contractno + ";" + transferdate.ToString("yyyy-MM-dd") + ";" + transferamount.ToString() + ";";
                                }
                                else
                                {

                                    DataRow drDiffBD = dtDiffBD.NewRow();
                                    drDiffBD[DAL.ContractTransfer.CONST_TransferDate] = transferdate;
                                    drDiffBD[DAL.ContractTransfer.CONST_TransferMoney] = transferamount;
                                    drDiffBD[DAL.ContractTransfer.CONST_ContractNo] = contractno;
                                    drDiffBD[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                                    drDiffBD[DAL.ContractTransfer.CONST_TransferType] = transfertype;
                                    drDiffBD[DAL.ContractTransfer.CONST_RemainMoney] = remainamount;
                                    drDiffBD[DAL.ContractTransfer.CONST_BDID] = drUpdated[DAL.ContractTransfer.CONST_BDID];
                                    drDiffBD["BDName"] = bdName;

                                    dtDiffBD.Rows.Add(drDiffBD);

                                }
                            }
                            else//如果部分转让, 用剩余金额修改OrionPrice. 如果有多个预合同的话, OrionPrice=剩余金额/预合同数
                            {
                                DAL.DicItem status = new DAL.DicItem();
                                status.DicItemID = (int)DicItem.PreContractStatus.PartWon;
                                precontract.StatusID = status;

                                //precontract.SalePrice = Convert.ToDecimal(transferamount);
                                //precontract.OrionPrice = Convert.ToDecimal(remainamount / iCount);
                                //precontract.CEWPromised = Convert.ToDecimal(remainamount);

                                //added by yuliang 2010-5-18,edited by yuliang 2010-8-26
                                if (bd != null && bd.BDID == precontract.BDID.BDID)
                                {
                                    precontract.OrionPrice = Convert.ToDecimal(remainamount / iCount);
                                    precontract.SparePrice = Convert.ToDecimal(remainamount);
                                    precontract.SalePrice = precontract.CEWPromised - precontract.SparePrice;
                                    precontract.TransferHistoryInfo += contractno + ";" + transferdate.ToString("yyyy-MM-dd") + ";" + transferamount.ToString() + ";";
                                }
                                else
                                {

                                    DataRow drDiffBD = dtDiffBD.NewRow();
                                    drDiffBD[DAL.ContractTransfer.CONST_TransferDate] = transferdate;
                                    drDiffBD[DAL.ContractTransfer.CONST_TransferMoney] = transferamount;
                                    drDiffBD[DAL.ContractTransfer.CONST_ContractNo] = contractno;
                                    drDiffBD[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                                    drDiffBD[DAL.ContractTransfer.CONST_TransferType] = transfertype;
                                    drDiffBD[DAL.ContractTransfer.CONST_RemainMoney] = remainamount;
                                    drDiffBD[DAL.ContractTransfer.CONST_BDID] = drUpdated[DAL.ContractTransfer.CONST_BDID];
                                    drDiffBD["BDName"] = bdName;

                                    dtDiffBD.Rows.Add(drDiffBD);
                                }

                            }
                            precontract.ModifiedDate = DateTime.Now;

                            //if (strSpecialApprove != "")
                            //    precontract.SpecialApprove = strSpecialApprove;

                            precontract.Update();
                        }
                    }
                    else
                    {
                        //else 不存在 1. 在dtMissing里加入一条记录
                        DataRow drMissing = dtMissing.NewRow();
                        drMissing[DAL.ContractTransfer.CONST_TransferDate] = transferdate;
                        drMissing[DAL.ContractTransfer.CONST_TransferMoney] = transferamount;
                        drMissing[DAL.ContractTransfer.CONST_ContractNo] = contractno;
                        drMissing[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                        drMissing[DAL.ContractTransfer.CONST_TransferType] = transfertype;
                        drMissing[DAL.ContractTransfer.CONST_RemainMoney] = remainamount;
                        drMissing["BDName"] = bdName;

                        dtMissing.Rows.Add(drMissing);

                    }
                }
            }
        }


        [Transaction(TransactionMode.Requires)]
        public void UploadPreContractCommentsInfo(int employeeId, DataTable dtUpload, out DataTable dtMissing, out DataTable dtUpdated, out string strMessage)
        {
            strMessage = string.Empty;

            using (ISession session = factory.OpenSession())
            {
                DataSet ds = new DataSet();

                //Create a parameter
                string strSql = SQL_SELECT_COMMENTS;

                strSql += " WHERE " + "1=2";

                dtMissing = DAO.ExecuteDataTable(session, strSql);

                dtUpdated = dtMissing.Clone();

                if (dtUpdated == null) return;

                IEmployee employeeservice = (IEmployee)AppContainer.Instance[Services.CONST_Employee_Service];
                string strpermission = employeeservice.GetDataPermission(employeeId);


                //循环上传的数据.
                foreach (DataRow drUpload in dtUpload.Rows)
                {
                    string precontractno = drUpload[0].ToString().Trim();
                    if (precontractno == string.Empty)
                        continue;
                    string bdcode = drUpload[1].ToString();
                    DateTime createddate = DateTime.Now;

                    DateTime promiseddate = DateTime.Now;
                    if (drUpload[2].ToString() != string.Empty)
                        promiseddate = Convert.ToDateTime(drUpload[2].ToString());

                    string comments = drUpload[3].ToString();

                    DAL.BD bd = DAL.BD.FindFirst(new EqExpression(DAL.BD.CONST_BDCode, bdcode));

                    if (bd == null) //bd不存在. 
                    {
                        //else 不存在 1. 在dtMissing里加入一条记录
                        DataRow drMissing = dtMissing.NewRow();
                        drMissing[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                        drMissing[DAL.BD.CONST_BDCode] = bdcode;
                        drMissing[DAL.PreContractInfoComment.CONST_DateCreated] = createddate;
                        drMissing[DAL.PreContractInfoComment.CONST_Comment] = comments;
                        drMissing[DAL.PreContractInfoComment.CONST_CommentPromisedDate] = promiseddate;
                        dtMissing.Rows.Add(drMissing);

                        continue;
                    }

                    string filter = string.Empty;

                    if (strpermission == string.Empty)
                        filter = string.Format(" {0} = '{1}' AND {2} = {3} ", DAL.PreContractInfo.CONST_PreContractNo, precontractno, DAL.BD.CONST_BDID, bd.BDID);
                    else
                        filter = strpermission + string.Format(" AND {0} = '{1}' AND {2} = {3} ", DAL.PreContractInfo.CONST_PreContractNo, precontractno, DAL.BD.CONST_BDID, bd.BDID);

                    DataTable dt = GetPreContractInfo(filter);

                    if (dt.Rows.Count > 0)
                    {

                        DAL.PreContractInfo precontract = DAL.PreContractInfo.FindFirst(new EqExpression(DAL.PreContractInfo.CONST_PreContractNo, precontractno), new EqExpression(DAL.PreContractInfo.CONST_BDID, bd));


                        //判断预合同是否存在.
                        if (precontract != null)
                        {

                            //if 存在 .新增Comments
                            DAL.PreContractInfoComment precontractComment = DAL.PreContractInfoComment.FindFirst(new EqExpression(DAL.PreContractInfoComment.CONST_PreContractID, precontract), new EqExpression(DAL.PreContractInfoComment.CONST_CommentPromisedDate, new NullableDateTime(promiseddate)), new EqExpression(DAL.PreContractInfoComment.CONST_Comment, comments));

                            if (precontractComment == null)
                            {
                                precontractComment = new DAL.PreContractInfoComment();
                                precontractComment.PreContractID = precontract;
                                precontractComment.Comment = comments;
                                DAL.Employee employee = new DAL.Employee();
                                employee.EmployeeID = employeeId;
                                precontractComment.Creator = employee;
                                precontractComment.DateCreated = createddate;
                                precontractComment.ModifiedBy = employee;
                                precontractComment.DateModified = DateTime.Now;
                                precontractComment.CommentPromisedDate = promiseddate;
                                precontractComment.IsRisk = false;

                                precontractComment.Create();


                                precontract.IsRisk = false;
                                precontract.Update();

                            }


                            //3. 在dtUpdate中加入一条记录
                            DataRow drUpdated = dtUpdated.NewRow();
                            drUpdated[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                            drUpdated[DAL.BD.CONST_BDCode] = bdcode;
                            drUpdated[DAL.PreContractInfoComment.CONST_DateCreated] = createddate;
                            drUpdated[DAL.PreContractInfoComment.CONST_Comment] = comments;
                            drUpdated[DAL.PreContractInfoComment.CONST_CommentPromisedDate] = promiseddate;
                            dtUpdated.Rows.Add(drUpdated);
                        }
                        else
                        {
                            //else 不存在 1. 在dtMissing里加入一条记录
                            DataRow drMissing = dtMissing.NewRow();
                            drMissing[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                            drMissing[DAL.BD.CONST_BDCode] = bdcode;
                            drMissing[DAL.PreContractInfoComment.CONST_DateCreated] = createddate;
                            drMissing[DAL.PreContractInfoComment.CONST_Comment] = comments;
                            dtMissing.Rows.Add(drMissing);
                        }
                    }
                    else
                    {
                        //else 不存在 1. 在dtMissing里加入一条记录
                        DataRow drMissing = dtMissing.NewRow();
                        drMissing[DAL.PreContractInfo.CONST_PreContractNo] = precontractno;
                        drMissing[DAL.BD.CONST_BDCode] = bdcode;
                        drMissing[DAL.PreContractInfoComment.CONST_DateCreated] = createddate;
                        drMissing[DAL.PreContractInfoComment.CONST_Comment] = comments;
                        dtMissing.Rows.Add(drMissing);
                    }

                }

            }
        }

        [Transaction(TransactionMode.Requires)]
        public void ChangeAssignment(string type, string oldId, string newId)
        {
            using (ISession session = factory.OpenSession())
            {
                if (type.Equals("1"))
                    type = "CAT";
                else if (type.Equals("2"))
                    type = "BD";
                else
                    return;

                string id = type + "ID";
                //Create a parameter
                string strSql = String.Format("UPDATE PreContractInfo SET {0} = {1} WHERE {2} = {3} AND (StatusID = 10 OR StatusID = 11)",
                    id, newId, id, oldId);

                DAO.ExecuteNonQuery(session, strSql);
            }
        }

        //Joan 2008-10-20
        [Transaction(TransactionMode.Requires)]
        public void UpdatePreContractRiskStat(DAL.PreContractInfo precontract)
        {
            using (ISession session = factory.OpenSession())
            {
                string isRisk;
                if (precontract.IsRisk)
                    isRisk = "1";
                else
                    isRisk = "0";


                //Create a parameter
                string strSql = String.Format("UPDATE PreContractInfo SET IsRisk = {0} WHERE  PreContractID= {1}",
                    isRisk, precontract.PreContractID.ToString());

                DAO.ExecuteNonQuery(session, strSql);
            }

        }

        public bool IsUnique(bool isNew, string preContractNo, int bdid)
        {
            DAL.BD bd = new DAL.BD();
            bd.BDID = bdid;

            DAL.PreContractInfo[] precontracts = DAL.PreContractInfo.FindAll(new EqExpression(DAL.PreContractInfo.CONST_PreContractNo, preContractNo), new EqExpression(DAL.PreContractInfo.CONST_BDID, bd));

            if (precontracts == null)
                return true;

            if (precontracts.Length == 0)
                return true;
            else if (precontracts.Length == 1)
            {
                if (isNew)
                    return false;
                else
                    return true;
            }
            else if (precontracts.Length > 1)
                return false;
            else
                return true;




        }

        //yuliang 2010-5-17
        [Transaction(TransactionMode.Requires)]
        public DataTable GetByContractNoAndBDID(string contractNo, int bdID)
        {
            using (ISession session = factory.OpenSession())
            {
                DataSet ds = new DataSet();

                string strSql = String.Format(" select * from dbo.PreContractInfo where PreContractNo = '{0}' and BDID = {1} ", contractNo, bdID);

                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch;
            }
        }

        [Transaction(TransactionMode.Requires)]
        public DataTable QuickSearchPreContract(string dpProvinces, string dpCAT, string dpFeedbackStatus, string dpExistMonth, int employeeId, bool isAdmin)
        {
            using (ISession session = factory.OpenSession())
            {
                string strWhere = string.Empty;

                if (!string.IsNullOrEmpty(dpFeedbackStatus))
                {
                    if (Convert.ToInt32(dpFeedbackStatus) == 1)
                    {
                        strWhere += " and a.PreContractID in (select PreContractID from vw_FeedbackPreContract)";
                    }
                    else
                    {
                        strWhere += " and a.PreContractID in (select PreContractID from vw_NoFeedbackPreContract)";
                    }
                }

                if (!string.IsNullOrEmpty(dpProvinces))
                {
                    strWhere += string.Format(" and a.ProvinceID = {0}", dpProvinces);
                }

                if (!string.IsNullOrEmpty(dpCAT))
                {
                    strWhere += string.Format(" and a.CATID = {0}", dpCAT);
                }

                if (!string.IsNullOrEmpty(dpExistMonth))
                {
                    switch (Convert.ToInt32(dpExistMonth))
                    {
                        case 1:
                            strWhere += " and a.ExistMonth < 1";
                            break;
                        case 2:
                            strWhere += " and a.ExistMonth in (1,2,3)";
                            break;
                        case 3:
                            strWhere += " and a.ExistMonth in (4,5,6)";
                            break;
                        case 4:
                            strWhere += " and a.ExistMonth in (7,8,9)";
                            break;
                        case 5:
                            strWhere += " and a.ExistMonth in (10,11,12)";
                            break;
                        default:
                            strWhere += " and a.ExistMonth > 12";
                            break;
                    }
                }

                string strSql = string.Empty;
                if (isAdmin)
                {
                    strSql = "select a.* from vw_PreContractInfo a";
                }
                else
                {
                    strSql = "select a.* from vw_PreContractInfo a inner join";
                    strSql += string.Format(" (select distinct * from vw_BDCATProvince where EmployeeID = {0} ) as b", employeeId);
                    strSql += " on b.BDID = a.BDID and b.CATID = a.CATID and b.ProvinceID = a.ProvinceID ";
                }

                strSql += " where a.StatusID in(10,11)" + strWhere;
                strSql += " order by PreContractNo";

                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);
                return dtSearch;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        [Transaction(TransactionMode.Requires)]
        public DataTable GetTransferredPreContract(string preContractNo, string contractStatus, string startDate, string endDate)
        {
            using (ISession session = factory.OpenSession())
            {
                string strWhere = string.Empty;

                if (!string.IsNullOrEmpty(preContractNo))
                {
                    strWhere += string.Format(" and PreContractNo like '%{0}%'", preContractNo);
                }
                if (!string.IsNullOrEmpty(contractStatus))
                {
                    strWhere += string.Format(" and StatusID = {0}", contractStatus);
                }
                if (!string.IsNullOrEmpty(startDate))
                {
                    strWhere += string.Format(" and TransferDate >= '{0}'", startDate);
                }
                if (!string.IsNullOrEmpty(endDate))
                {
                    strWhere += string.Format(" and TransferDate <= '{0}'", endDate);
                }

                string strSql = SQL_SELECT_TRANSFERREDCONTRACT + " where 1=1" + strWhere;

                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);
                return dtSearch;
            }
        }

        [Transaction(TransactionMode.Requires)]
        public DataTable GetPreContractList(string report, string filter, string employeeID)
        {
            using (ISession session = factory.OpenSession())
            {
                string strSql = "select * from vw_PreContractInfo AS a";
                strSql += " INNER JOIN ProvinceCluster AS b ON b.IsActive=1 AND b.ProvinceClusterID=a.ClusterID";
                strSql += string.Format(" INNER JOIN dbo.GetAssignedProvince({0}) AS c ON c.Active=1 AND a.ProvinceID=c.ProvinceID", employeeID);

                if (filter != string.Empty)
                {
                    strSql += "Where ";
                    strSql += filter;
                }
                strSql += " ORDER BY StatusID, PreContractNo ";

                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);
                return dtSearch;
            }
        }
    }
}